package com.example.stroebackstage.dao;

import com.example.stroebackstage.entity.Commodity;
import com.example.stroebackstage.utils.LinkMySQL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class CommoditiesDao {
    //    增
    public static boolean insertCommodity(Commodity commodity){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql = "insert into commodities(category, name, img,model, introduction ,price)values (?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,commodity.getCategory());
            pstmt.setString(2,commodity.getName());
            pstmt.setString(3,commodity.getImg());
            pstmt.setString(4,commodity.getModel());
            pstmt.setString(5,commodity.getIntroduction());
            pstmt.setDouble(6,commodity.getPrice());
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb>0;
    }
    //    删
    public static boolean delCommodity(int id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql =
                    "delete from commodities where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb>0;
    }
    //    改
    public static boolean updateCommodity(Commodity commodity){
        Connection conn = null;
        PreparedStatement pstmt = null;
        int numb = 0;
        try {
            conn = LinkMySQL.getConnection();
            String sql =
                    "update commodities set category=?, name=? ,img=? ,model=? ,introduction=? ,price=? where id=?" ;
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,commodity.getCategory());
            pstmt.setString(2,commodity.getName());
            pstmt.setString(3,commodity.getImg());
            pstmt.setString(4,commodity.getModel());
            pstmt.setString(5,commodity.getIntroduction());
            pstmt.setDouble(6,commodity.getPrice());
            pstmt.setInt(7,commodity.getId());
            numb = pstmt.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            LinkMySQL.release(pstmt,conn);
        }
        return numb>0;
    }
    //查全部
    public static ArrayList<Commodity> selectCommodity(){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Commodity> commoditiesList = new ArrayList<>();
        try {
            conn = LinkMySQL.getConnection();
            String sql = "select co.*,ct.type_name from commodities as co right join commoditytype as ct on co.category=ct.type_id";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Commodity commodity = new Commodity(rs.getInt("co.id"), rs.getInt("co.category"),rs.getString("ct.type_name"),rs.getString("co.name"), rs.getString("co.img"), rs.getString("co.model"), rs.getString("co.introduction"), rs.getDouble("co.price"));
                commoditiesList.add(commodity);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            LinkMySQL.release(rs,pstmt,conn);
        }
        return commoditiesList;
    }
//    根据id绝对查
    public static ArrayList<Commodity> selectCommodity(int id){
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    ArrayList<Commodity> commoditiesList = new ArrayList<>();
    try {
        conn = LinkMySQL.getConnection();
        String sql = "select co.*,ct.type_name from commodities as co right join commoditytype as ct on co.category=ct.type_id where co.id = ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1,id);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            Commodity commodity = new Commodity(rs.getInt("co.id"), rs.getInt("co.category"),rs.getString("ct.type_name"),rs.getString("co.name"), rs.getString("co.img"), rs.getString("co.model"), rs.getString("co.introduction"), rs.getDouble("co.price"));
            commoditiesList.add(commodity);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        LinkMySQL.release(rs,pstmt,conn);
    }
    return commoditiesList;
}
//    根据商品名称模糊查询
    public static ArrayList<Commodity> selectCommodity(String name){
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    ArrayList<Commodity> commoditiesList = new ArrayList<>();
    try {
        conn = LinkMySQL.getConnection();
        String sql = "select co.*,ct.type_name from commodities as co right join commoditytype as ct on co.category=ct.type_id where co.name like ?";
        pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,"%"+name+"%");
        rs = pstmt.executeQuery();
        while (rs.next()) {
            Commodity commodity = new Commodity(rs.getInt("co.id"), rs.getInt("co.category"),rs.getString("ct.type_name"),rs.getString("co.name"), rs.getString("co.img"), rs.getString("co.model"), rs.getString("co.introduction"), rs.getDouble("co.price"));
            commoditiesList.add(commodity);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        LinkMySQL.release(rs,pstmt,conn);
    }
    return commoditiesList;
}
}
